﻿using CCWin;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace TelephoneYiShou.XQL
{
    public partial class FrmShoppingPlan : Skin_Mac
    {
        public FrmShoppingPlan()
        {
            InitializeComponent();
        }
        DataTable dt;
        string sql;
        int i, start;//i表示总行数，start表示当前位置
        int size = 8;//size表示一页显示的数量
        //显示i到j的数据
        public void show(int i, int j)
        {
            SqlDataAdapter da = DBHelper.GetDataTableFenYe(sql);
            DataSet ds = new DataSet();
            da.Fill(ds, i, j, "[SellGoods]");//把数据库表中的i-j的记录数用来填充数据集
            dataGridView1.DataSource = ds.Tables["[SellGoods]"];
            ds = null;//清空数据集
        }
        private void FrmShoppingPlan_Load(object sender, EventArgs e)
        {
            sql = "select purchaseplanid '进货计划编号',c.CommodityName '商品名称',s.SupplierName '供应商',p.Getnumber '进货数量',p.OrderTime '最晚进货时间',p.Remark '备注',p.complete '是否完成',c.Colour '颜色'from purchaseplan p,Commodity c, Supplier s where p.CommodityID = c.CommodityID and p.SupplierID = s.SupplierID";
            dt = DBHelper.GetDataTable(sql);
            i = dt.Rows.Count;//获取数据的条数
            show(0, size);
            labelcount.Text = string.Format("第{0}页/共{1}页 共{2}条数据", start + 1, i / size + 1, i);//显示数据
            //商品名称绑定
            string sqlname = "select distinct commodityname from commodity";
            DataTable dte = DBHelper.GetDataTable(sqlname);
            for (int i = 0; i < dte.Rows.Count; i++)
            {
                textcommid.Items.Add(dte.Rows[i][0]);
            }
            //供应商绑定
            string sqlgys = "select suppliername from supplier";
            dte = DBHelper.GetDataTable(sqlgys);
            for (int i = 0; i < dte.Rows.Count; i++)
            {
                textgsid.Items.Add(dte.Rows[i][0]);
            }
        }
        //点击显示数据
        private void dataGridView1_Click(object sender, EventArgs e)
        {
            laid.Text = dataGridView1.SelectedRows[0].Cells[0].Value.ToString();
            textcommid.Text = dataGridView1.SelectedRows[0].Cells[1].Value.ToString();
            textgsid.Text = dataGridView1.SelectedRows[0].Cells[2].Value.ToString();
            textnum.Text = dataGridView1.SelectedRows[0].Cells[3].Value.ToString();
            textjtime.Text = dataGridView1.SelectedRows[0].Cells[4].Value.ToString(); 
            textbeizhu.Text = dataGridView1.SelectedRows[0].Cells[5].Value.ToString();
            comboBox1.Text = dataGridView1.SelectedRows[0].Cells[6].Value.ToString();
            txtcolor.Text = dataGridView1.SelectedRows[0].Cells[7].Value.ToString();
        }
        //修改
        private void buttupdate_Click(object sender, EventArgs e)
        {
            if (laid.Text == "请选择")
            {
                MessageBoxEx.Show("请先选择要改的计划");
                return;
            }
            string ids = laid.Text;
            string num = textnum.Text;
            string jtime = textjtime.Text;
            string beuzhu = textbeizhu.Text;
            string shifo = comboBox1.Text;
            if (string.IsNullOrEmpty(textcommid.Text)||string.IsNullOrEmpty(txtcolor.Text) || string.IsNullOrEmpty(textgsid.Text) || string.IsNullOrEmpty(ids) || string.IsNullOrEmpty(num) || string.IsNullOrEmpty(jtime) || string.IsNullOrEmpty(beuzhu) || string.IsNullOrEmpty(shifo))
            {
                MessageBoxEx.Show("请把数据填写完整");
                return;
            }
            string sqlnames = string.Format("select commodityid from commodity where commodityname='{0}' and colour = '{1}'", textcommid.Text, txtcolor.Text);
            string sqlgyss = string.Format("select supplierid from supplier where suppliername = '{0}'", textgsid.Text);
            string commid = DBHelper.GetDataTable(sqlnames).Rows[0][0].ToString();
            string gsid = DBHelper.GetDataTable(sqlgyss).Rows[0][0].ToString();
            string usql = string.Format("update purchaseplan set commodityid = '{0}',supplierid = '{1}',getnumber = '{2}',ordertime = '{3}',remark = '{4}', complete = '{5}' where purchaseplanid = '{6}'", commid, gsid, num, jtime, beuzhu, shifo, ids);
            if (DBHelper.ExecuteNonQuery(usql))
            {
                MessageBoxEx.Show("修改成功");
                start = 0;//初始化页数
                dt = DBHelper.GetDataTable(sql);
                i = dt.Rows.Count;//获取数据的条数
                show(0, size);
                labelcount.Text = string.Format("第{0}页/共{1}页 共{2}条数据", start + 1, i / size + 1, i);//显示数据

            }
            else
            {
                MessageBoxEx.Show("修改失败");
            }

        }
        //退出页面
        private void button1_Click(object sender, EventArgs e)
        {
            this.Close();
        }
        //删除
        private void buttdelete_Click(object sender, EventArgs e)
        {
            if (laid.Text == "请选择")
            {
                MessageBoxEx.Show("请先选择要删除的计划");
                return;
            }
            string ids = laid.Text;
            if (string.IsNullOrEmpty(ids))
            {
                MessageBoxEx.Show("请选中要删除的内容");
                return;
            }
            string dsql = string.Format("delete from purchaseplan where purchaseplanid = '{0}'", ids);
            DialogResult s = MessageBoxEx.Show("确定删除编号为" + ids + "的进货计划吗？", "提醒", MessageBoxButtons.YesNo);
            if (s == DialogResult.Yes)
            {
                if (DBHelper.ExecuteNonQuery(dsql))
                {
                    MessageBoxEx.Show("删除成功");
                    start = 0;//初始化页数
                    dt = DBHelper.GetDataTable(sql);
                    i = dt.Rows.Count;//获取数据的条数
                    show(0, size);
                    labelcount.Text = string.Format("第{0}页/共{1}页 共{2}条数据", start + 1, i / size + 1, i);//显示数据
                }
                else
                {
                    MessageBoxEx.Show("删除失败");
                }
            }
        }
        //添加
        private void buttadd_Click(object sender, EventArgs e)
        {
            FrmShoppingPlanAdd fspa = new FrmShoppingPlanAdd(dataGridView1);
            fspa.Show();
        }

        private void txtcolor_MouseEnter(object sender, EventArgs e)
        {
            //颜色数据绑定
            txtcolor.Items.Clear();
            string sqlcolor = string.Format("select colour from commodity where commodityname = '{0}'", textcommid.Text);
            DataTable dtes = DBHelper.GetDataTable(sqlcolor);
            for (int i = 0; i < dtes.Rows.Count; i++)
            {
                txtcolor.Items.Add(dtes.Rows[i][0]);
            }
        }
        //下一页
        private void NextPage_Click(object sender, EventArgs e)
        {
            if (start + 1 > i / size)
            {
                MessageBoxEx.Show("已是最后页");
            }
            else
            {
                start = start + 1;
                show(start * size, size);
            }
            labelcount.Text = string.Format("第{0}页/共{1}页 共{2}条数据", start + 1, i / size + 1, i);//显示数据
        }
        //查找
        private void buttselect_Click(object sender, EventArgs e)
        {
            start = 0;//初始化页数
            string gonshang = textgs.Text;
            if (string.IsNullOrEmpty(gonshang))
            {
                MessageBoxEx.Show("请输入要搜索的内容");
                return;
            }
            //按商品名称
            if (comanxuanxiang.Text == "商品名称")
            {
                sql = string.Format("select purchaseplanid '进货计划编号',c.CommodityName '商品名称',s.SupplierName '供应商',p.Getnumber '进货数量',p.OrderTime '最晚进货时间',p.Remark '备注',p.complete '是否完成',c.Colour '颜色'from purchaseplan p,Commodity c, Supplier s where p.CommodityID = c.CommodityID and p.SupplierID = s.SupplierID and c.CommodityName like '%{0}%'", gonshang);
                dt = DBHelper.GetDataTable(sql);
                i = dt.Rows.Count;//获取数据的条数
                if (i == 0)
                {
                    MessageBoxEx.Show("没有" + gonshang + "的订单");
                }
                else
                {
                    show(0, size);
                    labelcount.Text = string.Format("第{0}页/共{1}页 共{2}条数据", start + 1, i / size + 1, i);//显示数据
                }
            }
            //按供应商
            if (comanxuanxiang.Text == "供应商")
            {
                sql = string.Format("select purchaseplanid '进货计划编号',c.CommodityName '商品名称',s.SupplierName '供应商',p.Getnumber '进货数量',p.OrderTime '最晚进货时间',p.Remark '备注',p.complete '是否完成',c.Colour '颜色'from purchaseplan p,Commodity c, Supplier s where p.CommodityID = c.CommodityID and p.SupplierID = s.SupplierID and s.SupplierName like '%{0}%'", gonshang);
                dt = DBHelper.GetDataTable(sql);
                i = dt.Rows.Count;//获取数据的条数
                if (i == 0)
                {
                    MessageBoxEx.Show("没有" + gonshang + "的订单");
                }
                else
                {
                    show(0, size);
                    labelcount.Text = string.Format("第{0}页/共{1}页 共{2}条数据", start + 1, i / size + 1, i);//显示数据
                }
            }
            //按最晚时间
            if (comanxuanxiang.Text == "最晚进货时间")
            {
                try
                {
                    sql = string.Format("select purchaseplanid '进货计划编号',c.CommodityName '商品名称',s.SupplierName '供应商',p.Getnumber '进货数量',p.OrderTime '最晚进货时间',p.Remark '备注',p.complete '是否完成',c.Colour '颜色'from purchaseplan p,Commodity c, Supplier s where p.CommodityID = c.CommodityID and p.SupplierID = s.SupplierID and p.OrderTime <= '{0}'", gonshang);
                    dt = DBHelper.GetDataTable(sql);
                    i = dt.Rows.Count;//获取数据的条数
                    if (i == 0)
                    {
                        MessageBoxEx.Show("没有" + gonshang + "的订单");
                    }
                    else
                    {
                        show(0, size);
                        labelcount.Text = string.Format("第{0}页/共{1}页 共{2}条数据", start + 1, i / size + 1, i);//显示数据
                    }
                }
                catch (Exception)
                {

                    MessageBoxEx.Show("请输入正确的日期！");
                }
                
            }
            //按是否完成
            if (comanxuanxiang.Text == "是否完成")
            {
                sql = string.Format("select purchaseplanid '进货计划编号',c.CommodityName '商品名称',s.SupplierName '供应商',p.Getnumber '进货数量',p.OrderTime '最晚进货时间',p.Remark '备注',p.complete '是否完成',c.Colour '颜色'from purchaseplan p,Commodity c, Supplier s where p.CommodityID = c.CommodityID and p.SupplierID = s.SupplierID and p.complete = '{0}'", gonshang);
                dt = DBHelper.GetDataTable(sql);
                i = dt.Rows.Count;//获取数据的条数
                if (i == 0)
                {
                    MessageBoxEx.Show("没有" + gonshang + "的订单");
                }
                else
                {
                    show(0, size);
                    labelcount.Text = string.Format("第{0}页/共{1}页 共{2}条数据", start + 1, i / size + 1, i);//显示数据
                }
            }
        }
        //刷新
        private void skinButton1_Click(object sender, EventArgs e)
        {
            sql = "select purchaseplanid '进货计划编号',c.CommodityName '商品名称',s.SupplierName '供应商',p.Getnumber '进货数量',p.OrderTime '最晚进货时间',p.Remark '备注',p.complete '是否完成',c.Colour '颜色'from purchaseplan p,Commodity c, Supplier s where p.CommodityID = c.CommodityID and p.SupplierID = s.SupplierID";
            dt = DBHelper.GetDataTable(sql);
            i = dt.Rows.Count;//获取数据的条数
            show(0, size);
            labelcount.Text = string.Format("第{0}页/共{1}页 共{2}条数据", start + 1, i / size + 1, i);//显示数据
        }

        private void UpPgae_Click(object sender, EventArgs e)
        {

            if (start < 1)
            {
                MessageBoxEx.Show("已是第一页......");
            }
            else
            {
                start = start - 1;
                show(start * size, size);
            }
            labelcount.Text = string.Format("第{0}页/共{1}页 共{2}条数据", start + 1, i / size + 1, i);//显示数据
        }
    }
}
